﻿using System;
using System.Data;
using JTLeigh.Commerce.Spencer.Core.Common;
using JTLeigh.Commerce.Spencer.Core.Common.Services;
using JTLeigh.Commerce.Spencer.Core.Data;
using JTLeigh.Commerce.Spencer.Core.Data.Services;
using JTLeigh.Commerce.Spencer.Features.ProductFeedback.Common;
using JTLeigh.Commerce.Spencer.Features.ProductFeedback.Common.Entities;
using Microsoft.Practices.Unity;

namespace JTLeigh.Commerce.Spencer.Features.ProductFeedback.Data.Providers
{

	/// <summary>
	/// An <see cref="IProductFeedbackMetadataProvider"/> implementation that retrieves product 
	/// feedback metadata from the database.
	/// </summary>
	public class ProductFeedbackMetadataProvider : IProductFeedbackMetadataProvider
	{

		#region Inner Classes and Types

		internal static class StoredProcedure
		{

			public static class GetProductFeedbackMetadata
			{

				public const string CommandName = "GetProductFeedbackMetadata";

				public static class ParameterName
				{
					public const string TenantId = "@TenantId";
				}

				public static class ColumnName
				{

                    public static class ProductFeedbackBadge
                    {
                        public const string BadgeId = "BadgeId";
                        public const string WellKnownBadgeCode = "WellKnownBadgeCode";
                    }

					public static class ProductRatingAttribute
					{
						public const string AttributeId = "AttributeId";
						public const string DefaultText = "DefaultText";
					}

					public static class ProductRatingAttributeLocalization
					{
						public const string CultureId = "CultureId";
						public const string Text = "Text";
					}

					public static class ProductRecommendationAttribute
					{
						public const string AttributeId = "AttributeId";
						public const string DefaultText = "DefaultText";
					}

					public static class ProductRecommendationAttributeLocalization
					{
						public const string CultureId = "CultureId";
						public const string Text = "Text";
					}

					public static class ProductReviewAttribute
					{
						public const string AttributeId = "AttributeId";
						public const string DefaultText = "DefaultText";
					}

					public static class ProductReviewAttributeLocalization
					{
						public const string CultureId = "CultureId";
						public const string Text = "Text";
					}

				}

			}

            public static class GetProductFeedbackBadgeProductRelations
            {

                public const string CommandName = "GetProductFeedbackBadgeProductRelations";

                public static class ParameterName
                {
                    public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string ProductId = "@ProductId";
                }

                public static class ColumnNames
                {

                    public static class ProductFeedbackBadgeProductAssociation
                    {
                        public const string BadgeId = "BadgeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductFeedbackBadgeProductDisassociation
                    {
                        public const string BadgeId = "BadgeId";
                    }

                }

            }

            public static class GetProductFeedbackBadgeCategoryRelations
            {

                public const string CommandName = "GetProductFeedbackBadgeCategoryRelations";

                public static class ParameterName
                {
                    public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string CategoryId = "@CategoryId";
                }

                public static class ColumnNames
                {

                    public static class ProductFeedbackBadgeCategoryAssociation
                    {
                        public const string BadgeId = "BadgeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductFeedbackBadgeCategoryDisassociation
                    {
                        public const string BadgeId = "BadgeId";
                    }

                }

            }

            public static class GetProductRatingAttributeProductRelations
            {

                public const string CommandName = "GetProductRatingAttributeProductRelations";

                public static class ParameterName
                {
                    public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string ProductId = "@ProductId";
                }

                public static class ColumnNames
                {

                    public static class ProductRatingAttributeProductAssociation
                    {
                        public const string AttributeId = "AttributeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductRatingAttributeProductDisassociation
                    {
                        public const string AttributeId = "AttributeId";
                    }

                }

            }

            public static class GetProductRatingAttributeCategoryRelations
            {

                public const string CommandName = "GetProductRatingAttributeCategoryRelations";

                public static class ParameterName
                {
                    public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string CategoryId = "@CategoryId";
                }

                public static class ColumnNames
                {

                    public static class ProductRatingAttributeCategoryAssociation
                    {
                        public const string AttributeId = "AttributeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductRatingAttributeCategoryDisassociation
                    {
                        public const string AttributeId = "AttributeId";
                    }

                }

            }

            public static class GetProductRecommendationAttributeProductRelations
			{

				public const string CommandName = "GetProductRecommendationAttributeProductRelations";

				public static class ParameterName
				{
					public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string ProductId = "@ProductId";
                }

				public static class ColumnNames
				{

                    public static class ProductRecommendationAttributeProductAssociation
                    {
                        public const string AttributeId = "AttributeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductRecommendationAttributeProductDisassociation
                    {
                        public const string AttributeId = "AttributeId";
                    }

                }

			}

			public static class GetProductRecommendationAttributeCategoryRelations
			{

				public const string CommandName = "GetProductRecommendationAttributeCategoryRelations";

				public static class ParameterName
				{
					public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string CategoryId = "@CategoryId";
                }

				public static class ColumnNames
				{

                    public static class ProductRecommendationAttributeCategoryAssociation
                    {
                        public const string AttributeId = "AttributeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductRecommendationAttributeCategoryDisassociation
                    {
                        public const string AttributeId = "AttributeId";
                    }

                }

			}

			public static class GetProductReviewAttributeProductRelations
			{

				public const string CommandName = "GetProductReviewAttributeProductRelations";

				public static class ParameterName
				{
					public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string ProductId = "@ProductId";
                }

				public static class ColumnNames
				{

                    public static class ProductReviewAttributeProductAssociation
                    {
                        public const string AttributeId = "AttributeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductReviewAttributeProductDisassociation
                    {
                        public const string AttributeId = "AttributeId";
                    }

                }

			}

			public static class GetProductReviewAttributeCategoryRelations
			{

				public const string CommandName = "GetProductReviewAttributeCategoryRelations";

				public static class ParameterName
				{
                    public const string TenantId = "@TenantId";
                    public const string CatalogId = "@CatalogId";
                    public const string CategoryId = "@CategoryId";
				}

				public static class ColumnNames
				{

                    public static class ProductReviewAttributeCategoryAssociation
                    {
                        public const string AttributeId = "AttributeId";
                        public const string DisplayOrder = "DisplayOrder";
                    }

                    public static class ProductReviewAttributeCategoryDisassociation
                    {
                        public const string AttributeId = "AttributeId";
                    }

                }

			}

		}

		#endregion // Inner Classes and Types

		#region Constants

		/// <summary>
		/// The database key to use to create database connections for performing read operations.
		/// </summary>
		protected internal const string DatabaseKeyReadOperations = "ProductFeedbackMetadataReadOperations";

		/// <summary>
		/// The schema name to qualify commands with.
		/// </summary>
		protected internal const string DatabaseSchema = "ProductFeedback";

		#endregion // Constants

		#region Properties

		/// <summary>
		/// Gets or sets the database connection factory to use to create connections to databases.
		/// </summary>
		/// <value>An <see cref="IDatabaseConnectionFactory"/> implementation.</value>
		[Dependency]
		public IDatabaseConnectionFactory DatabaseConnectionFactory { get; set; }

		/// <summary>
		/// Gets or sets the exception handling service to use to handle exceptions.
		/// </summary>
		/// <value>An <see cref="IExceptionHandlingService"/> implementation.</value>
		[Dependency]
		public IExceptionHandlingService ExceptionHandlingService { get; set; }

		#endregion // Properties

		#region IProductFeedbackMetadataProvider Members

		/// <summary>
		/// Gets all product feedback metadata from the database.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
		/// metadata is to be retrieved.</param>
		/// <returns>A <see cref="ProductFeedbackMetadata"/> containing all rating attributes,
		/// recommendation attributes, review attributes, etc.</returns>
		public ProductFeedbackMetadata GetProductFeedbackMetadata(Guid tenantId)
		{
			IDbCommand command;
			ProductFeedbackMetadata metadata;

			try
			{
				using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
				{
					connection.Open();
					try
					{
						using (command = CreateGetProductFeedbackMetadataCommand(connection, tenantId))
						{
							metadata = ReadProductFeedbackMetadata(command);
						}
						return metadata;
					}
					finally
					{
						connection.Close();
					}
				}
			}
			catch (Exception e)
			{
				ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
				throw;
			}
		}

        /// <summary>
        /// Gets all of the associations and disassociations of the specified product to and from 
        /// product feedback badges.
        /// </summary>
        /// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
        /// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the product whose relations are to be retrieved is in.</param>
        /// <param name="productId">The product identifier <see cref="String"/> of the product 
        /// whose relations are to be retrieved.</param>
        /// <returns>A <see cref="ProductFeedbackBadgeProductRelations"/> object.</returns>
        public ProductFeedbackBadgeProductRelations GetProductFeedbackBadgeProductRelations(Guid tenantId, string catalogId, string productId)
        {
            IDbCommand command;
            ProductFeedbackBadgeProductRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("productId", productId);

            try
            {
                using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
                {
                    connection.Open();
                    try
                    {
                        using (command = CreateProductFeedbackBadgeProductRelationsCommand(connection, tenantId, catalogId, productId))
                        {
                            relations = ReadProductFeedbackBadgeProductRelations(command);
                        }
                        return relations;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            catch (Exception e)
            {
                ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
                throw;
            }
        }

        /// <summary>
        /// Gets all of the associations and disassociations of the specified category to and from 
        /// product feedback badges.
        /// </summary>
        /// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
        /// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the category whose relations are to be retrieved is in.</param>
        /// <param name="categoryId">The category identifier <see cref="String"/> of the category 
        /// whose relations are to be retrieved.</param>
        /// <returns>A <see cref="ProductFeedbackBadgeCategoryRelations"/> object.</returns>
        public ProductFeedbackBadgeCategoryRelations GetProductFeedbackBadgeCategoryRelations(Guid tenantId, string catalogId, string categoryId)
        {
            IDbCommand command;
            ProductFeedbackBadgeCategoryRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("categoryId", categoryId);

            try
            {
                using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
                {
                    connection.Open();
                    try
                    {
                        using (command = CreateProductFeedbackBadgeCategoryRelationsCommand(connection, tenantId, catalogId, categoryId))
                        {
                            relations = ReadProductFeedbackBadgeCategoryRelations(command);
                        }
                        return relations;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            catch (Exception e)
            {
                ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
                throw;
            }
        }

        /// <summary>
		/// Gets all of the associations and disassociations of the specified product to and from 
		/// product rating attributes.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
		/// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the product whose relations are to be retrieved is in.</param>
        /// <param name="productId">The product identifier <see cref="String"/> of the product 
		/// whose relations are to be retrieved.</param>
		/// <returns>A <see cref="ProductRatingAttributeProductRelations"/> object.</returns>
		public ProductRatingAttributeProductRelations GetProductRatingAttributeProductRelations(Guid tenantId, string catalogId, string productId)
		{
			IDbCommand command;
			ProductRatingAttributeProductRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("productId", productId);

			try
			{
				using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
				{
					connection.Open();
					try
					{
						using (command = CreateProductRatingAttributeProductRelationsCommand(connection, tenantId, catalogId, productId))
						{
							relations = ReadProductRatingAttributeProductRelations(command);
						}
						return relations;
					}
					finally
					{
						connection.Close();
					}
				}
			}
			catch (Exception e)
			{
				ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
				throw;
			}
		}

		/// <summary>
		/// Gets all of the associations and disassociations of the specified category to and from 
		/// product rating attributes.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
		/// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the category whose relations are to be retrieved is in.</param>
        /// <param name="categoryId">The category identifier <see cref="String"/> of the category 
		/// whose relations are to be retrieved.</param>
		/// <returns>A <see cref="ProductRatingAttributeCategoryRelations"/> object.</returns>
		public ProductRatingAttributeCategoryRelations GetProductRatingAttributeCategoryRelations(Guid tenantId, string catalogId, string categoryId)
		{
			IDbCommand command;
			ProductRatingAttributeCategoryRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("categoryId", categoryId);

			try
			{
				using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
				{
					connection.Open();
					try
					{
						using (command = CreateProductRatingAttributeCategoryRelationsCommand(connection, tenantId, catalogId, categoryId))
						{
							relations = ReadProductRatingAttributeCategoryRelations(command);
						}
						return relations;
					}
					finally
					{
						connection.Close();
					}
				}
			}
			catch (Exception e)
			{
				ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
				throw;
			}
		}

		/// <summary>
		/// Gets all of the associations and disassociations of the specified product to and from 
		/// product recommendation attributes.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
		/// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the product whose relations are to be retrieved is in.</param>
        /// <param name="productId">The product identifier <see cref="String"/> of the product 
		/// whose relations are to be retrieved.</param>
		/// <returns>A <see cref="ProductRecommendationAttributeProductRelations"/> object.</returns>
		public ProductRecommendationAttributeProductRelations GetProductRecommendationAttributeProductRelations(Guid tenantId, string catalogId, string productId)
		{
			IDbCommand command;
			ProductRecommendationAttributeProductRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("productId", productId);

			try
			{
				using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
				{
					connection.Open();
					try
					{
						using (command = CreateProductRecommendationAttributeProductRelationsCommand(connection, tenantId, catalogId, productId))
						{
							relations = ReadProductRecommendationAttributeProductRelations(command);
						}
						return relations;
					}
					finally
					{
						connection.Close();
					}
				}
			}
			catch (Exception e)
			{
				ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
				throw;
			}
		}

		/// <summary>
		/// Gets all of the associations and disassociations of the specified category to and from 
		/// product recommendation attributes.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
		/// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the category whose relations are to be retrieved is in.</param>
        /// <param name="categoryId">The category identifier <see cref="String"/> of the category 
		/// whose relations are to be retrieved.</param>
		/// <returns>A <see cref="ProductRecommendationAttributeCategoryRelations"/> object.</returns>
		public ProductRecommendationAttributeCategoryRelations GetProductRecommendationAttributeCategoryRelations(Guid tenantId, string catalogId, string categoryId)
		{
			IDbCommand command;
			ProductRecommendationAttributeCategoryRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("categoryId", categoryId);

			try
			{
				using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
				{
					connection.Open();
					try
					{
						using (command = CreateProductRecommendationAttributeCategoryRelationsCommand(connection, tenantId, catalogId, categoryId))
						{
							relations = ReadProductRecommendationAttributeCategoryRelations(command);
						}
						return relations;
					}
					finally
					{
						connection.Close();
					}
				}
			}
			catch (Exception e)
			{
				ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
				throw;
			}
		}

		/// <summary>
		/// Gets all of the associations and disassociations of the specified product to and from 
		/// product review attributes.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
		/// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the product whose relations are to be retrieved is in.</param>
        /// <param name="productId">The product identifier <see cref="String"/> of the product 
		/// whose relations are to be retrieved.</param>
		/// <returns>A <see cref="ProductReviewAttributeProductRelations"/> object.</returns>
		public ProductReviewAttributeProductRelations GetProductReviewAttributeProductRelations(Guid tenantId, string catalogId, string productId)
		{
			IDbCommand command;
			ProductReviewAttributeProductRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("productId", productId);

			try
			{
				using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
				{
					connection.Open();
					try
					{
						using (command = CreateProductReviewAttributeProductRelationsCommand(connection, tenantId, catalogId, productId))
						{
							relations = ReadProductReviewAttributeProductRelations(command);
						}
						return relations;
					}
					finally
					{
						connection.Close();
					}
				}
			}
			catch (Exception e)
			{
				ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
				throw;
			}
		}

		/// <summary>
		/// Gets all of the associations and disassociations of the specified category to and from 
		/// product review attributes.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant whose
		/// relations are to be retrieved.</param>
        /// <param name="catalogId">The catalog identifier <see cref="String"/> of the catalog
        /// that the category whose relations are to be retrieved is in.</param>
        /// <param name="categoryId">The category identifier <see cref="String"/> of the category 
		/// whose relations are to be retrieved.</param>
		/// <returns>A <see cref="ProductReviewAttributeCategoryRelations"/> object.</returns>
		public ProductReviewAttributeCategoryRelations GetProductReviewAttributeCategoryRelations(Guid tenantId, string catalogId, string categoryId)
		{
			IDbCommand command;
			ProductReviewAttributeCategoryRelations relations;

            Safety.AssertArgumentNotNull("catalogId", catalogId);
            Safety.AssertArgumentNotNull("categoryId", categoryId);

			try
			{
				using (IDbConnection connection = CreateDatabaseConnectionReadOperations(tenantId))
				{
					connection.Open();
					try
					{
						using (command = CreateProductReviewAttributeCategoryRelationsCommand(connection, tenantId, catalogId, categoryId))
						{
							relations = ReadProductReviewAttributeCategoryRelations(command);
						}
						return relations;
					}
					finally
					{
						connection.Close();
					}
				}
			}
			catch (Exception e)
			{
				ExceptionHandlingService.HandleException(tenantId, e, ProductFeedbackExceptionPolicy.Data);
				throw;
			}
		}

		#endregion // IProductFeedbackMetadataProvider Members

		#region Implementation

		/// <summary>
		/// Creates a connection to the database to be used for performing read operations.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant who the
		/// database connection is being created for.</param>
		/// <returns>An <see cref="IDbConnection"/> connection to the specified database.</returns>
		protected virtual IDbConnection CreateDatabaseConnectionReadOperations(Guid tenantId)
		{
			return DatabaseConnectionFactory.CreateDatabaseConnection(tenantId, DatabaseKeyReadOperations);
		}

		/// <summary>
		/// Gets the fully-qualified command name.
		/// </summary>
		/// <param name="tenantId">The tenant identifier <see cref="Guid"/> of the tenant who the
		/// command name is being qualified for.</param>
		/// <param name="commandName">The command name to qualify.</param>
		/// <returns>The fully-qualified command name.</returns>
		protected virtual string GetFullyQualifiedCommandName(Guid tenantId, string commandName)
		{
			return String.Concat("[", DatabaseSchema, "].[", commandName, "]");
		}

		#region GetProductFeedbackMetadata

		private IDbCommand CreateGetProductFeedbackMetadataCommand(IDbConnection connection, Guid tenantId)
		{
			IDbCommand command;
			IDbDataParameter parameter;

			Safety.AssertArgumentNotNull("connection", connection);

			command = connection.CreateCommand();
			command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductFeedbackMetadata.CommandName);
			command.CommandType = CommandType.StoredProcedure;

			parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductFeedbackMetadata.ParameterName.TenantId;
			parameter.Value = tenantId;
			command.Parameters.Add(parameter);

			return command;
		}

		private static ProductFeedbackMetadata ReadProductFeedbackMetadata(IDbCommand command)
		{
			ProductFeedbackMetadata metadata;

			Safety.AssertArgumentNotNull("command", command);

			metadata = new ProductFeedbackMetadata();
			using (IDataReader reader = command.ExecuteReader())
			{
				try
				{
                    ReadBadges(reader, metadata);
                    Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackMetadata.CommandName + " returned only 1 of the 7 expected result sets.");

                    ReadRatingAttributes(reader, metadata);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackMetadata.CommandName + " returned only 2 of the 7 expected result sets.");

					ReadRatingAttributeLocalizations(reader, metadata);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackMetadata.CommandName + " returned only 3 of the 7 expected result sets.");

					ReadRecommendationAttributes(reader, metadata);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackMetadata.CommandName + " returned only 4 of the 7 expected result sets.");

					ReadRecommendationAttributeLocalizations(reader, metadata);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackMetadata.CommandName + " returned only 5 of the 7 expected result sets.");

					ReadReviewAttributes(reader, metadata);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackMetadata.CommandName + " returned only 6 of the 7 expected result sets.");

					ReadReviewAttributeLocalizations(reader, metadata);
					Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductFeedbackMetadata.CommandName + " returned more than the 7 expected result sets.");
				}
				finally
				{
					reader.Close();
				}
			}
			return metadata;
		}

        private static void ReadBadges(IDataReader reader, ProductFeedbackMetadata metadata)
        {
            ProductFeedbackBadge badge;

            Safety.AssertArgumentNotNull("reader", reader);
            Safety.AssertArgumentNotNull("metadata", metadata);

            while (reader.Read())
            {
                badge = ReadBadge(reader);
                metadata.Badges.Add(badge.BadgeId, badge);
            }
        }

        private static ProductFeedbackBadge ReadBadge(IDataReader reader)
        {
            ProductFeedbackBadge badge;

            Safety.AssertArgumentNotNull("reader", reader);

            badge = new ProductFeedbackBadge();
            badge.BadgeId = (Guid)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductFeedbackBadge.BadgeId];
            badge.WellKnownBadgeCode = (byte)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductFeedbackBadge.WellKnownBadgeCode];
            return badge;
        }

        private static void ReadRatingAttributes(IDataReader reader, ProductFeedbackMetadata metadata)
        {
            ProductRatingAttribute ratingAttribute;

            Safety.AssertArgumentNotNull("reader", reader);
            Safety.AssertArgumentNotNull("metadata", metadata);

            while (reader.Read())
            {
                ratingAttribute = ReadRatingAttribute(reader);
                metadata.RatingAttributes.Add(ratingAttribute.AttributeId, ratingAttribute);
            }
        }

        private static ProductRatingAttribute ReadRatingAttribute(IDataReader reader)
        {
            ProductRatingAttribute ratingAttribute;

            Safety.AssertArgumentNotNull("reader", reader);

            ratingAttribute = new ProductRatingAttribute();
            ratingAttribute.AttributeId = (Guid)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRatingAttribute.AttributeId];
            ratingAttribute.DefaultText = (string)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRatingAttribute.DefaultText];
            return ratingAttribute;
        }

        private static void ReadRatingAttributeLocalizations(IDataReader reader, ProductFeedbackMetadata metadata)
		{
			ProductRatingAttributeLocalization ratingAttributeLocalization;
			ProductRatingAttribute ratingAttribute;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("metadata", metadata);

			while (reader.Read())
			{
				ratingAttributeLocalization = ReadRatingAttributeLocalization(reader);
				ratingAttribute = metadata.RatingAttributes[ratingAttributeLocalization.AttributeId];
				ratingAttribute.Localizations.Add(ratingAttributeLocalization.CultureId, ratingAttributeLocalization);
			}
		}

		private static ProductRatingAttributeLocalization ReadRatingAttributeLocalization(IDataReader reader)
		{
			ProductRatingAttributeLocalization localization;

			Safety.AssertArgumentNotNull("reader", reader);

			localization = new ProductRatingAttributeLocalization();
			localization.AttributeId = (Guid)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRatingAttribute.AttributeId];
			localization.CultureId = (int)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRatingAttributeLocalization.CultureId];
			localization.Text = (string)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRatingAttributeLocalization.Text];
			return localization;
		}

		private static void ReadRecommendationAttributes(IDataReader reader, ProductFeedbackMetadata metadata)
		{
			ProductRecommendationAttribute recommendationAttribute;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("metadata", metadata);

			while (reader.Read())
			{
				recommendationAttribute = ReadRecommendationAttribute(reader);
				metadata.RecommendationAttributes.Add(recommendationAttribute.AttributeId, recommendationAttribute);
			}
		}

		private static ProductRecommendationAttribute ReadRecommendationAttribute(IDataReader reader)
		{
			ProductRecommendationAttribute recommendationAttribute;

			Safety.AssertArgumentNotNull("reader", reader);

			recommendationAttribute = new ProductRecommendationAttribute();
			recommendationAttribute.AttributeId = (Guid)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRecommendationAttribute.AttributeId];
			recommendationAttribute.DefaultText = (string)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRecommendationAttribute.DefaultText];
			return recommendationAttribute;
		}

		private static void ReadRecommendationAttributeLocalizations(IDataReader reader, ProductFeedbackMetadata metadata)
		{
			ProductRecommendationAttributeLocalization recommendationAttributeLocalization;
			ProductRecommendationAttribute recommendationAttribute;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("metadata", metadata);

			while (reader.Read())
			{
				recommendationAttributeLocalization = ReadRecommendationAttributeLocalization(reader);
				recommendationAttribute = metadata.RecommendationAttributes[recommendationAttributeLocalization.AttributeId];
				recommendationAttribute.Localizations.Add(recommendationAttributeLocalization.CultureId, recommendationAttributeLocalization);
			}
		}

		private static ProductRecommendationAttributeLocalization ReadRecommendationAttributeLocalization(IDataReader reader)
		{
			ProductRecommendationAttributeLocalization localization;

			Safety.AssertArgumentNotNull("reader", reader);

			localization = new ProductRecommendationAttributeLocalization();
			localization.AttributeId = (Guid)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRecommendationAttribute.AttributeId];
			localization.CultureId = (int)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRecommendationAttributeLocalization.CultureId];
			localization.Text = (string)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductRecommendationAttributeLocalization.Text];
			return localization;
		}

		private static void ReadReviewAttributes(IDataReader reader, ProductFeedbackMetadata metadata)
		{
			ProductReviewAttribute reviewAttribute;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("metadata", metadata);

			while (reader.Read())
			{
				reviewAttribute = ReadReviewAttribute(reader);
				metadata.ReviewAttributes.Add(reviewAttribute.AttributeId, reviewAttribute);
			}
		}

		private static ProductReviewAttribute ReadReviewAttribute(IDataReader reader)
		{
			ProductReviewAttribute reviewAttribute;

			Safety.AssertArgumentNotNull("reader", reader);

			reviewAttribute = new ProductReviewAttribute();
			reviewAttribute.AttributeId = (Guid)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductReviewAttribute.AttributeId];
			reviewAttribute.DefaultText = (string)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductReviewAttribute.DefaultText];
			return reviewAttribute;
		}

		private static void ReadReviewAttributeLocalizations(IDataReader reader, ProductFeedbackMetadata metadata)
		{
			ProductReviewAttributeLocalization reviewAttributeLocalization;
			ProductReviewAttribute reviewAttribute;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("metadata", metadata);

			while (reader.Read())
			{
				reviewAttributeLocalization = ReadReviewAttributeLocalization(reader);
				reviewAttribute = metadata.ReviewAttributes[reviewAttributeLocalization.AttributeId];
				reviewAttribute.Localizations.Add(reviewAttributeLocalization.CultureId, reviewAttributeLocalization);
			}
		}

		private static ProductReviewAttributeLocalization ReadReviewAttributeLocalization(IDataReader reader)
		{
			ProductReviewAttributeLocalization localization;

			Safety.AssertArgumentNotNull("reader", reader);

			localization = new ProductReviewAttributeLocalization();
			localization.AttributeId = (Guid)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductReviewAttribute.AttributeId];
			localization.CultureId = (int)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductReviewAttributeLocalization.CultureId];
			localization.Text = (string)reader[StoredProcedure.GetProductFeedbackMetadata.ColumnName.ProductReviewAttributeLocalization.Text];
			return localization;
		}

		#endregion // GetProductFeedbackMetadata

        #region GetProductFeedbackBadgeProductRelations

        private IDbCommand CreateProductFeedbackBadgeProductRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string productId)
        {
            IDbCommand command;
            IDbDataParameter parameter;

            Safety.AssertArgumentNotNull("connection", connection);

            command = connection.CreateCommand();
            command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductFeedbackBadgeProductRelations.CommandName);
            command.CommandType = CommandType.StoredProcedure;

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductFeedbackBadgeProductRelations.ParameterName.TenantId;
            parameter.Value = tenantId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductFeedbackBadgeProductRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductFeedbackBadgeProductRelations.ParameterName.ProductId;
            parameter.Value = productId;
            command.Parameters.Add(parameter);

            return command;
        }

        private static ProductFeedbackBadgeProductRelations ReadProductFeedbackBadgeProductRelations(IDbCommand command)
        {
            ProductFeedbackBadgeProductRelations relations;

            Safety.AssertArgumentNotNull("command", command);

            relations = new ProductFeedbackBadgeProductRelations();
            using (IDataReader reader = command.ExecuteReader())
            {
                try
                {
                    ReadProductFeedbackBadgeProductAssociations(reader, relations);
                    Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackBadgeProductRelations.CommandName + " returned only 1 of the 2 expected result sets.");

                    ReadProductFeedbackBadgeProductDisassociations(reader, relations);
                    Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductFeedbackBadgeProductRelations.CommandName + " returned more than the 2 expected result sets.");
                }
                finally
                {
                    reader.Close();
                }
            }
            return relations;
        }

        private static void ReadProductFeedbackBadgeProductAssociations(IDataReader reader, ProductFeedbackBadgeProductRelations relations)
        {
            ProductFeedbackBadgeProductAssociation association;

            Safety.AssertArgumentNotNull("reader", reader);
            Safety.AssertArgumentNotNull("relations", relations);

            while (reader.Read())
            {
                association = ReadProductFeedbackBadgeProductAssociation(reader);
                relations.Associations.Add(association);
            }
        }

        private static ProductFeedbackBadgeProductAssociation ReadProductFeedbackBadgeProductAssociation(IDataReader reader)
        {
            ProductFeedbackBadgeProductAssociation association;

            Safety.AssertArgumentNotNull("reader", reader);

            association = new ProductFeedbackBadgeProductAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductFeedbackBadgeProductRelations.ColumnNames.ProductFeedbackBadgeProductAssociation.BadgeId, association);
            return association;
        }

        private static void ReadProductFeedbackBadgeProductDisassociations(IDataReader reader, ProductFeedbackBadgeProductRelations relations)
        {
            ProductFeedbackBadgeProductDisassociation disassociation;

            Safety.AssertArgumentNotNull("reader", reader);
            Safety.AssertArgumentNotNull("relations", relations);

            while (reader.Read())
            {
                disassociation = ReadProductFeedbackBadgeProductDisassociation(reader);
                relations.Disassociations.Add(disassociation);
            }
        }

        private static ProductFeedbackBadgeProductDisassociation ReadProductFeedbackBadgeProductDisassociation(IDataReader reader)
        {
            ProductFeedbackBadgeProductDisassociation disassociation;

            Safety.AssertArgumentNotNull("reader", reader);

            disassociation = new ProductFeedbackBadgeProductDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductFeedbackBadgeProductRelations.ColumnNames.ProductFeedbackBadgeProductDisassociation.BadgeId, disassociation);
            return disassociation;
        }

        #endregion // GetProductFeedbackBadgeProductRelations

        #region GetProductFeedbackBadgeCategoryRelations

        private IDbCommand CreateProductFeedbackBadgeCategoryRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string categoryId)
        {
            IDbCommand command;
            IDbDataParameter parameter;

            Safety.AssertArgumentNotNull("connection", connection);

            command = connection.CreateCommand();
            command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductFeedbackBadgeCategoryRelations.CommandName);
            command.CommandType = CommandType.StoredProcedure;

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductFeedbackBadgeCategoryRelations.ParameterName.TenantId;
            parameter.Value = tenantId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductFeedbackBadgeCategoryRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductFeedbackBadgeCategoryRelations.ParameterName.CategoryId;
            parameter.Value = categoryId;
            command.Parameters.Add(parameter);

            return command;
        }

        private static ProductFeedbackBadgeCategoryRelations ReadProductFeedbackBadgeCategoryRelations(IDbCommand command)
        {
            ProductFeedbackBadgeCategoryRelations relations;

            Safety.AssertArgumentNotNull("command", command);

            relations = new ProductFeedbackBadgeCategoryRelations();
            using (IDataReader reader = command.ExecuteReader())
            {
                try
                {
                    ReadProductFeedbackBadgeCategoryAssociations(reader, relations);
                    Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductFeedbackBadgeCategoryRelations.CommandName + " returned only 1 of the 2 expected result sets.");

                    ReadProductFeedbackBadgeCategoryDisassociations(reader, relations);
                    Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductFeedbackBadgeCategoryRelations.CommandName + " returned more than the 2 expected result sets.");
                }
                finally
                {
                    reader.Close();
                }
            }
            return relations;
        }

        private static void ReadProductFeedbackBadgeCategoryAssociations(IDataReader reader, ProductFeedbackBadgeCategoryRelations relations)
        {
            ProductFeedbackBadgeCategoryAssociation association;

            Safety.AssertArgumentNotNull("reader", reader);
            Safety.AssertArgumentNotNull("relations", relations);

            while (reader.Read())
            {
                association = ReadProductFeedbackBadgeCategoryAssociation(reader);
                relations.Associations.Add(association);
            }
        }

        private static ProductFeedbackBadgeCategoryAssociation ReadProductFeedbackBadgeCategoryAssociation(IDataReader reader)
        {
            ProductFeedbackBadgeCategoryAssociation association;

            Safety.AssertArgumentNotNull("reader", reader);

            association = new ProductFeedbackBadgeCategoryAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductFeedbackBadgeCategoryRelations.ColumnNames.ProductFeedbackBadgeCategoryAssociation.BadgeId, association);
            return association;
        }

        private static void ReadProductFeedbackBadgeCategoryDisassociations(IDataReader reader, ProductFeedbackBadgeCategoryRelations relations)
        {
            ProductFeedbackBadgeCategoryDisassociation disassociation;

            Safety.AssertArgumentNotNull("reader", reader);
            Safety.AssertArgumentNotNull("relations", relations);

            while (reader.Read())
            {
                disassociation = ReadProductFeedbackBadgeCategoryDisassociation(reader);
                relations.Disassociations.Add(disassociation);
            }
        }

        private static ProductFeedbackBadgeCategoryDisassociation ReadProductFeedbackBadgeCategoryDisassociation(IDataReader reader)
        {
            ProductFeedbackBadgeCategoryDisassociation disassociation;

            Safety.AssertArgumentNotNull("reader", reader);

            disassociation = new ProductFeedbackBadgeCategoryDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductFeedbackBadgeCategoryRelations.ColumnNames.ProductFeedbackBadgeCategoryDisassociation.BadgeId, disassociation);
            return disassociation;
        }

        #endregion // GetProductFeedbackBadgeCategoryRelations

        #region GetProductRatingAttributeProductRelations

		private IDbCommand CreateProductRatingAttributeProductRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string productId)
		{
			IDbCommand command;
			IDbDataParameter parameter;

			Safety.AssertArgumentNotNull("connection", connection);

			command = connection.CreateCommand();
			command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductRatingAttributeProductRelations.CommandName);
			command.CommandType = CommandType.StoredProcedure;

			parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductRatingAttributeProductRelations.ParameterName.TenantId;
			parameter.Value = tenantId;
			command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductRatingAttributeProductRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductRatingAttributeProductRelations.ParameterName.ProductId;
			parameter.Value = productId;
			command.Parameters.Add(parameter);

			return command;
		}

		private static ProductRatingAttributeProductRelations ReadProductRatingAttributeProductRelations(IDbCommand command)
		{
			ProductRatingAttributeProductRelations relations;

			Safety.AssertArgumentNotNull("command", command);

			relations = new ProductRatingAttributeProductRelations();
			using (IDataReader reader = command.ExecuteReader())
			{
				try
				{
					ReadProductRatingAttributeProductAssociations(reader, relations);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductRatingAttributeProductRelations.CommandName + " returned only 1 of the 2 expected result sets.");

					ReadProductRatingAttributeProductDisassociations(reader, relations);
					Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductRatingAttributeProductRelations.CommandName + " returned more than the 2 expected result sets.");
				}
				finally
				{
					reader.Close();
				}
			}
			return relations;
		}

		private static void ReadProductRatingAttributeProductAssociations(IDataReader reader, ProductRatingAttributeProductRelations relations)
		{
			ProductRatingAttributeProductAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				association = ReadProductRatingAttributeProductAssociation(reader);
				relations.Associations.Add(association);
			}
		}

		private static ProductRatingAttributeProductAssociation ReadProductRatingAttributeProductAssociation(IDataReader reader)
		{
			ProductRatingAttributeProductAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);

			association = new ProductRatingAttributeProductAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductRatingAttributeProductRelations.ColumnNames.ProductRatingAttributeProductAssociation.AttributeId, association);
			association.DisplayOrder = (int)reader[StoredProcedure.GetProductRatingAttributeProductRelations.ColumnNames.ProductRatingAttributeProductAssociation.DisplayOrder];
			return association;
		}

		private static void ReadProductRatingAttributeProductDisassociations(IDataReader reader, ProductRatingAttributeProductRelations relations)
		{
			ProductRatingAttributeProductDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				disassociation = ReadProductRatingAttributeProductDisassociation(reader);
				relations.Disassociations.Add(disassociation);
			}
		}

		private static ProductRatingAttributeProductDisassociation ReadProductRatingAttributeProductDisassociation(IDataReader reader)
		{
			ProductRatingAttributeProductDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);

			disassociation = new ProductRatingAttributeProductDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductRatingAttributeProductRelations.ColumnNames.ProductRatingAttributeProductDisassociation.AttributeId, disassociation);
			return disassociation;
		}

		#endregion // GetProductRatingAttributeProductRelations

		#region GetProductRatingAttributeCategoryRelations

		private IDbCommand CreateProductRatingAttributeCategoryRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string categoryId)
		{
			IDbCommand command;
			IDbDataParameter parameter;

			Safety.AssertArgumentNotNull("connection", connection);

            command = connection.CreateCommand();
            command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductRatingAttributeCategoryRelations.CommandName);
            command.CommandType = CommandType.StoredProcedure;

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductRatingAttributeCategoryRelations.ParameterName.TenantId;
            parameter.Value = tenantId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductRatingAttributeCategoryRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductRatingAttributeCategoryRelations.ParameterName.CategoryId;
			parameter.Value = categoryId;
			command.Parameters.Add(parameter);

			return command;
		}

		private static ProductRatingAttributeCategoryRelations ReadProductRatingAttributeCategoryRelations(IDbCommand command)
		{
			ProductRatingAttributeCategoryRelations relations;

			Safety.AssertArgumentNotNull("command", command);

			relations = new ProductRatingAttributeCategoryRelations();
			using (IDataReader reader = command.ExecuteReader())
			{
				try
				{
					ReadProductRatingAttributeCategoryAssociations(reader, relations);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductRatingAttributeCategoryRelations.CommandName + " returned only 1 of the 2 expected result sets.");

					ReadProductRatingAttributeCategoryDisassociations(reader, relations);
					Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductRatingAttributeCategoryRelations.CommandName + " returned more than the 2 expected result sets.");
				}
				finally
				{
					reader.Close();
				}
			}
			return relations;
		}

		private static void ReadProductRatingAttributeCategoryAssociations(IDataReader reader, ProductRatingAttributeCategoryRelations relations)
		{
			ProductRatingAttributeCategoryAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				association = ReadProductRatingAttributeCategoryAssociation(reader);
				relations.Associations.Add(association);
			}
		}

		private static ProductRatingAttributeCategoryAssociation ReadProductRatingAttributeCategoryAssociation(IDataReader reader)
		{
			ProductRatingAttributeCategoryAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);

			association = new ProductRatingAttributeCategoryAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductRatingAttributeCategoryRelations.ColumnNames.ProductRatingAttributeCategoryAssociation.AttributeId, association);
			association.DisplayOrder = (int)reader[StoredProcedure.GetProductRatingAttributeCategoryRelations.ColumnNames.ProductRatingAttributeCategoryAssociation.DisplayOrder];
			return association;
		}

		private static void ReadProductRatingAttributeCategoryDisassociations(IDataReader reader, ProductRatingAttributeCategoryRelations relations)
		{
			ProductRatingAttributeCategoryDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				disassociation = ReadProductRatingAttributeCategoryDisassociation(reader);
				relations.Disassociations.Add(disassociation);
			}
		}

		private static ProductRatingAttributeCategoryDisassociation ReadProductRatingAttributeCategoryDisassociation(IDataReader reader)
		{
			ProductRatingAttributeCategoryDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);

			disassociation = new ProductRatingAttributeCategoryDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductRatingAttributeCategoryRelations.ColumnNames.ProductRatingAttributeCategoryDisassociation.AttributeId, disassociation);
			return disassociation;
		}

		#endregion // GetProductRatingAttributeCategoryRelations

		#region GetProductRecommendationAttributeProductRelations

		private IDbCommand CreateProductRecommendationAttributeProductRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string productId)
		{
			IDbCommand command;
			IDbDataParameter parameter;

			Safety.AssertArgumentNotNull("connection", connection);

			command = connection.CreateCommand();
			command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductRecommendationAttributeProductRelations.CommandName);
			command.CommandType = CommandType.StoredProcedure;

			parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductRecommendationAttributeProductRelations.ParameterName.TenantId;
			parameter.Value = tenantId;
			command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductRecommendationAttributeProductRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductRecommendationAttributeProductRelations.ParameterName.ProductId;
            parameter.Value = productId;
            command.Parameters.Add(parameter);

			return command;
		}

		private static ProductRecommendationAttributeProductRelations ReadProductRecommendationAttributeProductRelations(IDbCommand command)
		{
			ProductRecommendationAttributeProductRelations relations;

			Safety.AssertArgumentNotNull("command", command);

			relations = new ProductRecommendationAttributeProductRelations();
			using (IDataReader reader = command.ExecuteReader())
			{
				try
				{
					ReadProductRecommendationAttributeProductAssociations(reader, relations);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductRecommendationAttributeProductRelations.CommandName + " returned only 1 of the 2 expected result sets.");

					ReadProductRecommendationAttributeProductDisassociations(reader, relations);
					Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductRecommendationAttributeProductRelations.CommandName + " returned more than the 2 expected result sets.");
				}
				finally
				{
					reader.Close();
				}
			}
			return relations;
		}

		private static void ReadProductRecommendationAttributeProductAssociations(IDataReader reader, ProductRecommendationAttributeProductRelations relations)
		{
			ProductRecommendationAttributeProductAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				association = ReadProductRecommendationAttributeProductAssociation(reader);
				relations.Associations.Add(association);
			}
		}

		private static ProductRecommendationAttributeProductAssociation ReadProductRecommendationAttributeProductAssociation(IDataReader reader)
		{
			ProductRecommendationAttributeProductAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);

			association = new ProductRecommendationAttributeProductAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductRecommendationAttributeProductRelations.ColumnNames.ProductRecommendationAttributeProductAssociation.AttributeId, association);
			association.DisplayOrder = (int)reader[StoredProcedure.GetProductRecommendationAttributeProductRelations.ColumnNames.ProductRecommendationAttributeProductAssociation.DisplayOrder];
			return association;
		}

		private static void ReadProductRecommendationAttributeProductDisassociations(IDataReader reader, ProductRecommendationAttributeProductRelations relations)
		{
			ProductRecommendationAttributeProductDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				disassociation = ReadProductRecommendationAttributeProductDisassociation(reader);
				relations.Disassociations.Add(disassociation);
			}
		}

		private static ProductRecommendationAttributeProductDisassociation ReadProductRecommendationAttributeProductDisassociation(IDataReader reader)
		{
			ProductRecommendationAttributeProductDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);

			disassociation = new ProductRecommendationAttributeProductDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductRecommendationAttributeProductRelations.ColumnNames.ProductRecommendationAttributeProductDisassociation.AttributeId, disassociation);
			return disassociation;
		}

		#endregion // GetProductRecommendationAttributeProductRelations

		#region GetProductRecommendationAttributeCategoryRelations

		private IDbCommand CreateProductRecommendationAttributeCategoryRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string categoryId)
		{
			IDbCommand command;
			IDbDataParameter parameter;

			Safety.AssertArgumentNotNull("connection", connection);

			command = connection.CreateCommand();
			command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductRecommendationAttributeCategoryRelations.CommandName);
			command.CommandType = CommandType.StoredProcedure;

			parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductRecommendationAttributeCategoryRelations.ParameterName.TenantId;
			parameter.Value = tenantId;
			command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductRecommendationAttributeCategoryRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

			parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductRecommendationAttributeCategoryRelations.ParameterName.CategoryId;
			parameter.Value = categoryId;
			command.Parameters.Add(parameter);

			return command;
		}

		private static ProductRecommendationAttributeCategoryRelations ReadProductRecommendationAttributeCategoryRelations(IDbCommand command)
		{
			ProductRecommendationAttributeCategoryRelations relations;

			Safety.AssertArgumentNotNull("command", command);

			relations = new ProductRecommendationAttributeCategoryRelations();
			using (IDataReader reader = command.ExecuteReader())
			{
				try
				{
					ReadProductRecommendationAttributeCategoryAssociations(reader, relations);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductRecommendationAttributeCategoryRelations.CommandName + " returned only 1 of the 2 expected result sets.");

					ReadProductRecommendationAttributeCategoryDisassociations(reader, relations);
					Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductRecommendationAttributeCategoryRelations.CommandName + " returned more than the 2 expected result sets.");
				}
				finally
				{
					reader.Close();
				}
			}
			return relations;
		}

		private static void ReadProductRecommendationAttributeCategoryAssociations(IDataReader reader, ProductRecommendationAttributeCategoryRelations relations)
		{
			ProductRecommendationAttributeCategoryAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				association = ReadProductRecommendationAttributeCategoryAssociation(reader);
				relations.Associations.Add(association);
			}
		}

		private static ProductRecommendationAttributeCategoryAssociation ReadProductRecommendationAttributeCategoryAssociation(IDataReader reader)
		{
			ProductRecommendationAttributeCategoryAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);

			association = new ProductRecommendationAttributeCategoryAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductRecommendationAttributeCategoryRelations.ColumnNames.ProductRecommendationAttributeCategoryAssociation.AttributeId, association);
			association.DisplayOrder = (int)reader[StoredProcedure.GetProductRecommendationAttributeCategoryRelations.ColumnNames.ProductRecommendationAttributeCategoryAssociation.DisplayOrder];
			return association;
		}

		private static void ReadProductRecommendationAttributeCategoryDisassociations(IDataReader reader, ProductRecommendationAttributeCategoryRelations relations)
		{
			ProductRecommendationAttributeCategoryDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				disassociation = ReadProductRecommendationAttributeCategoryDisassociation(reader);
				relations.Disassociations.Add(disassociation);
			}
		}

		private static ProductRecommendationAttributeCategoryDisassociation ReadProductRecommendationAttributeCategoryDisassociation(IDataReader reader)
		{
			ProductRecommendationAttributeCategoryDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);

			disassociation = new ProductRecommendationAttributeCategoryDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductRecommendationAttributeCategoryRelations.ColumnNames.ProductRecommendationAttributeCategoryDisassociation.AttributeId, disassociation);
			return disassociation;
		}

		#endregion // GetProductRecommendationAttributeCategoryRelations

		#region GetProductReviewAttributeProductRelations

		private IDbCommand CreateProductReviewAttributeProductRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string productId)
		{
			IDbCommand command;
			IDbDataParameter parameter;

			Safety.AssertArgumentNotNull("connection", connection);

			command = connection.CreateCommand();
			command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductReviewAttributeProductRelations.CommandName);
			command.CommandType = CommandType.StoredProcedure;

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductReviewAttributeProductRelations.ParameterName.TenantId;
            parameter.Value = tenantId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductReviewAttributeProductRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductReviewAttributeProductRelations.ParameterName.ProductId;
			parameter.Value = productId;
			command.Parameters.Add(parameter);

			return command;
		}

		private static ProductReviewAttributeProductRelations ReadProductReviewAttributeProductRelations(IDbCommand command)
		{
			ProductReviewAttributeProductRelations relations;

			Safety.AssertArgumentNotNull("command", command);

			relations = new ProductReviewAttributeProductRelations();
			using (IDataReader reader = command.ExecuteReader())
			{
				try
				{
					ReadProductReviewAttributeProductAssociations(reader, relations);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductReviewAttributeProductRelations.CommandName + " returned only 1 of the 2 expected result sets.");

					ReadProductReviewAttributeProductDisassociations(reader, relations);
					Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductReviewAttributeProductRelations.CommandName + " returned more than the 2 expected result sets.");
				}
				finally
				{
					reader.Close();
				}
			}
			return relations;
		}

		private static void ReadProductReviewAttributeProductAssociations(IDataReader reader, ProductReviewAttributeProductRelations relations)
		{
			ProductReviewAttributeProductAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				association = ReadProductReviewAttributeProductAssociation(reader);
				relations.Associations.Add(association);
			}
		}

		private static ProductReviewAttributeProductAssociation ReadProductReviewAttributeProductAssociation(IDataReader reader)
		{
			ProductReviewAttributeProductAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);

			association = new ProductReviewAttributeProductAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductReviewAttributeProductRelations.ColumnNames.ProductReviewAttributeProductAssociation.AttributeId, association);
			association.DisplayOrder = (int)reader[StoredProcedure.GetProductReviewAttributeProductRelations.ColumnNames.ProductReviewAttributeProductAssociation.DisplayOrder];
			return association;
		}

		private static void ReadProductReviewAttributeProductDisassociations(IDataReader reader, ProductReviewAttributeProductRelations relations)
		{
			ProductReviewAttributeProductDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				disassociation = ReadProductReviewAttributeProductDisassociation(reader);
				relations.Disassociations.Add(disassociation);
			}
		}

		private static ProductReviewAttributeProductDisassociation ReadProductReviewAttributeProductDisassociation(IDataReader reader)
		{
			ProductReviewAttributeProductDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);

			disassociation = new ProductReviewAttributeProductDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductReviewAttributeProductRelations.ColumnNames.ProductReviewAttributeProductDisassociation.AttributeId, disassociation);
            
            return disassociation;
		}

		#endregion // GetProductReviewAttributeProductRelations

		#region GetProductReviewAttributeCategoryRelations

		private IDbCommand CreateProductReviewAttributeCategoryRelationsCommand(IDbConnection connection, Guid tenantId, string catalogId, string categoryId)
		{
			IDbCommand command;
			IDbDataParameter parameter;

			Safety.AssertArgumentNotNull("connection", connection);

			command = connection.CreateCommand();
			command.CommandText = GetFullyQualifiedCommandName(tenantId, StoredProcedure.GetProductReviewAttributeCategoryRelations.CommandName);
			command.CommandType = CommandType.StoredProcedure;

			parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductReviewAttributeCategoryRelations.ParameterName.TenantId;
			parameter.Value = tenantId;
			command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = StoredProcedure.GetProductReviewAttributeCategoryRelations.ParameterName.CatalogId;
            parameter.Value = catalogId;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
			parameter.ParameterName = StoredProcedure.GetProductReviewAttributeCategoryRelations.ParameterName.CategoryId;
			parameter.Value = categoryId;
			command.Parameters.Add(parameter);

			return command;
		}

		private static ProductReviewAttributeCategoryRelations ReadProductReviewAttributeCategoryRelations(IDbCommand command)
		{
			ProductReviewAttributeCategoryRelations relations;

			Safety.AssertArgumentNotNull("command", command);

			relations = new ProductReviewAttributeCategoryRelations();
			using (IDataReader reader = command.ExecuteReader())
			{
				try
				{
					ReadProductReviewAttributeCategoryAssociations(reader, relations);
					Safety.AssertTrue(reader.NextResult(), StoredProcedure.GetProductReviewAttributeCategoryRelations.CommandName + " returned only 5 of the 6 expected result sets.");

					ReadProductReviewAttributeCategoryDisassociations(reader, relations);
					Safety.AssertFalse(reader.NextResult(), StoredProcedure.GetProductReviewAttributeCategoryRelations.CommandName + " returned more than the 6 expected result sets.");
				}
				finally
				{
					reader.Close();
				}
			}
			return relations;
		}

		private static void ReadProductReviewAttributeCategoryAssociations(IDataReader reader, ProductReviewAttributeCategoryRelations relations)
		{
			ProductReviewAttributeCategoryAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				association = ReadProductReviewAttributeCategoryAssociation(reader);
				relations.Associations.Add(association);
			}
		}

		private static ProductReviewAttributeCategoryAssociation ReadProductReviewAttributeCategoryAssociation(IDataReader reader)
		{
			ProductReviewAttributeCategoryAssociation association;

			Safety.AssertArgumentNotNull("reader", reader);

			association = new ProductReviewAttributeCategoryAssociation();
            RelationsProviderTools.ReadAssociation(reader, StoredProcedure.GetProductReviewAttributeCategoryRelations.ColumnNames.ProductReviewAttributeCategoryAssociation.AttributeId, association);
			association.DisplayOrder = (int)reader[StoredProcedure.GetProductReviewAttributeCategoryRelations.ColumnNames.ProductReviewAttributeCategoryAssociation.DisplayOrder];
			return association;
		}

		private static void ReadProductReviewAttributeCategoryDisassociations(IDataReader reader, ProductReviewAttributeCategoryRelations relations)
		{
			ProductReviewAttributeCategoryDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);
			Safety.AssertArgumentNotNull("relations", relations);

			while (reader.Read())
			{
				disassociation = ReadProductReviewAttributeCategoryDisassociation(reader);
				relations.Disassociations.Add(disassociation);
			}
		}

		private static ProductReviewAttributeCategoryDisassociation ReadProductReviewAttributeCategoryDisassociation(IDataReader reader)
		{
			ProductReviewAttributeCategoryDisassociation disassociation;

			Safety.AssertArgumentNotNull("reader", reader);

			disassociation = new ProductReviewAttributeCategoryDisassociation();
            RelationsProviderTools.ReadDisassociation(reader, StoredProcedure.GetProductReviewAttributeCategoryRelations.ColumnNames.ProductReviewAttributeCategoryDisassociation.AttributeId, disassociation);
            return disassociation;
		}

		#endregion // GetProductReviewAttributeCategoryRelations

		#endregion // Implementation

	}

}
